﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_BDDB_ByMid]
	(
	
	@userid VARCHAR(10),
	@StartDate NCHAR(10),
	@EndDate NCHAR(10),
	@CompanyId VARCHAR(500)	
	)
AS
BEGIN
DECLARE @strSql VARCHAR(max)


DECLARE @cid VARCHAR(8000)
	SELECT @cid=au.Cid FROM aspnet_Users au where au.username =@UserId
	IF @cid='[ALL]'
	BEGIN
		  SELECT 
 
  	@cid=
  	REPLACE(REPLACE(( SELECT id FROM Company  
                                        order by id asc FOR XML AUTO ), '<Company id="', '['), '"/>', ']')
	END

--SET @strSql='
;WITH list as (
SELECT 
       mid,[MName]
      ,SUM([xzcds]) [xzcds]
      ,SUM([sxs]) [sxs]
      ,SUM([zsxs]) [zsxs]
      ,SUM([xdxse]) [xdxse]
      ,SUM([xdml]) [xdml]
      ,SUM([xfxse]) [xfxse]
      ,SUM([xfml]) [xfml]
      ,SUM([xse]) [xse]
      ,SUM([ml]) [ml] from bddb WHERE startdate =@StartDate AND enddate=@EndDate
      
      and  CHARINDEX('['+CONVERT(VARCHAR, CompanyId)+']' , 
                       @cid,0)>0
      and mid in (28,27,26)
group by mid,MName )

select * from list,(select sum(xzcds) as xzcds1,sum(sxs) as sxs1,sum(zsxs) as zsxs1,sum(xdxse) as xdxse1,sum(xdml) as xdml1
, sum(xfxse) as xfxse1 ,sum(xfml) as xfml1,sum(xse) as xse1,sum(ml) as ml1 from list ) as tb  order by mid
--'
--EXEC(@strSql)
--PRINT (@strSql)
END
